package db

import (
	"database/sql"
	_ "github.com/lib/pq"
	"golang.org/x/text/cases"
	"golang.org/x/text/language"
	"strings"
	"ubt-gin-admin/app/pkg/conf"
	"ubt-gin-admin/app/pkg/enums"
	"ubt-gin-admin/app/pkg/logger"
)

var Conn *sql.DB

// InitPgsql 连接MySQL数据库
func InitPgsql() *sql.DB {
	cfg := conf.Conf.Pgsql
	var err error
	dataSrcName := "host=" + cfg.Host + " port=" + cfg.Port + " user=" + cfg.Usname + " password=" + cfg.Passwd + " dbname=" + cfg.Dbname + " sslmode=disable"
	if Conn, err = sql.Open("postgres", dataSrcName); err != nil {
		panic("数据库连接失败。" + err.Error())
		return nil
	}
	if err = Conn.Ping(); err != nil {
		panic("数据库Ping连接失败。" + err.Error())
		return nil
	}
	return Conn
}

// CudData 操作数据，增、删、改均调用该方法
func CudData(sqlStr string, args ...any) (string, string) {
	logger.Debug(sqlStr)
	logger.Debug(args)
	stmt, err := Conn.Prepare(sqlStr)
	if err != nil {
		logger.Error("数据库预处理失败。" + err.Error())
		return enums.DbErr.Code, enums.DbErr.Msg
	}
	defer func(stmt *sql.Stmt) {
		if err = stmt.Close(); err != nil {
			return
		}
	}(stmt)
	if _, err = stmt.Exec(args...); err == nil {
		return enums.Succeed.Code, enums.Succeed.Msg
	}
	logger.Error("数据处理失败。" + err.Error())
	return enums.DataErr.Code, enums.DataErr.Msg
}

// GetOne 获取一行记录
func GetOne(sqlStr string, args ...any) (string, string, map[string]any) {
	logger.Debug(sqlStr)
	logger.Debug(args)
	// 查询数据
	rows, err := Conn.Query(sqlStr, args...)
	// 最终关闭数据库连接
	defer func(rows *sql.Rows) {
		if err = rows.Close(); err != nil {
			logger.Error("关闭数据查询失败。" + err.Error())
			return
		}
	}(rows)
	if err == nil {
		var dbMap map[string]any
		for rows.Next() {
			dbMap = scanRow(rows)
			if dbMap != nil {
				break
			}
		}
		if len(dbMap) > 0 {
			return enums.Succeed.Code, enums.Succeed.Msg, dbMap
		}
		return enums.NoData.Code, enums.NoData.Msg, nil
	}
	logger.Error("数据获取失败。" + err.Error())
	return enums.NoData.Code, enums.NoData.Msg, nil
}

// GetAll 获取多行记录
func GetAll(sqlStr string, args ...any) (string, string, []map[string]any) {
	logger.Debug(sqlStr)
	logger.Debug(args)
	// 查询数据
	rows, err := Conn.Query(sqlStr, args...)
	// 最终关闭数据库连接
	defer func(rows *sql.Rows) {
		if err = rows.Close(); err != nil {
			logger.Error("关闭数据查询失败。" + err.Error())
			return
		}
	}(rows)
	if err == nil {
		result := make([]map[string]any, 0)
		var (
			dbMap map[string]any
			flag  uint8
		)
		for rows.Next() {
			if dbMap = scanRow(rows); dbMap != nil {
				result = append(result, dbMap)
			} else {
				flag = 1
				break
			}
		}
		if flag == 0 {
			return enums.Succeed.Code, enums.Succeed.Msg, result
		}
	}
	logger.Error("数据获取失败。" + err.Error())
	return enums.NoData.Code, enums.NoData.Msg, nil
}

// scanRow 将数据库字段转换为Map
func scanRow(rows *sql.Rows) map[string]any {
	if columns, err := rows.Columns(); err == nil {
		vals := make([]any, len(columns))
		valsPtr := make([]any, len(columns))
		for i := range vals {
			valsPtr[i] = &vals[i]
		}
		if err = rows.Scan(valsPtr...); err == nil {
			r := make(map[string]any)
			c := cases.Title(language.Dutch)
			for i, v := range columns {
				// 将数据表中各字段下划线转成驼峰
				v = strings.Replace(v, "_", " ", -1)
				v = c.String(v)
				v = strings.Replace(v, " ", "", -1)
				v = strings.ToLower(string(v[0])) + v[1:]
				if va, ok := vals[i].([]byte); ok {
					r[v] = string(va)
				} else {
					r[v] = vals[i]
				}
			}
			return r
		}
		logger.Error("数据字段转换失败。" + err.Error())
	}
	return nil
}

// SelDictsIds 获取部门及子部门数据编号
func SelDictsIds(id, typeCode any) []string {
	code, _, list := GetAll("SELECT dict_id, parent_id FROM sys_dict WHERE states=1 AND kind=2 AND type_code=$1 ORDER BY sort", typeCode)
	if code == enums.Succeed.Code {
		var newList []string
		newList = append(newList, id.(string))
		for _, item := range list {
			if item["parentId"] == id {
				newList = append(newList, item["dictId"].(string))
				newList = getIds(list, item["dictId"].(string), newList)
			}
		}
		return newList
	}
	return nil
}

// getIds 遍历获取子级数据编号
func getIds(list []map[string]any, id any, newList []string) []string {
	newList = append(newList, id.(string))
	for _, item := range list {
		if id == item["parentId"] {
			newList = append(newList, item["dictId"].(string))
			newList = getIds(list, item["dictId"], newList)
		}
	}
	return newList
}
